﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: May 2, 2016
-- Description:	Routine to update all EntityNames and LookupNames in the Entities Table - Uses ConvertNOSQL
-- =============================================
CREATE PROCEDURE [dbo].[UpdateEntityNames]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @mysql nvarchar(max), @EntityType nvarchar(255), @EntitySubType nvarchar(255), @DefinedEntityName nvarchar(255)

	DECLARE EntityTypesCursor cursor for
		SELECT dbo.DefinedEntityTypes.EntityType, dbo.DefinedEntitySubTypes.EntitySubType, 
			iif(dbo.DefinedEntitySubTypes.EntitySubType is null OR dbo.DefinedEntitySubTypes.DefinedEntityName is null, dbo.DefinedEntityTypes.DefinedEntityName, dbo.DefinedEntitySubTypes.DefinedEntityName) as DefEntityName
			FROM dbo.DefinedEntityTypes LEFT OUTER JOIN
				dbo.DefinedEntitySubTypes ON dbo.DefinedEntityTypes.EntityType = dbo.DefinedEntitySubTypes.EntityType
				WHERE DefinedEntityTypes.RafikiNOSQL is null
	OPEN EntityTypesCursor
	Fetch NEXT FROM EntityTypesCursor INTO @EntityType, @EntitySubType, @DefinedEntityName
	WHILE @@FETCH_STATUS = 0 BEGIN
	  IF (@DefinedEntityName is not null) BEGIN
		IF (@EntitySubType is null) BEGIN
			PRINT @EntityType
			SET @mysql = 'UPDATE E SET E.EntityName = N.EntityName, E.LookupName=N.LookupName FROM Entities E JOIN (SELECT EntityID, EntityName, LookupName FROM (' + 
				dbo.ConvertRafikiNOSQL('SELECT EntityID, EntityName,  LookupName FROM {' + @EntityType + '}',default,default,default,default) + 
				') X) N ON N.EntityID=E.EntityID'
		END
		ELSE BEGIN
			PRINT @EntityType + '.' + @EntitySubType
			SET @mysql = 'UPDATE E SET E.EntityName = N.EntityName, E.LookupName=N.LookupName FROM Entities E JOIN (SELECT EntityID, EntityName, LookupName FROM (' + 
				dbo.ConvertRafikiNOSQL('SELECT EntityID, EntityName, LookupName FROM {' + @EntityType + '.' + @EntitySubType + '}',default,default,default,default) + 
				') X) N ON N.EntityID=E.EntityID'
		END
		EXEC(@mysql)
		if (@@ERROR>0) PRINT @mysql
	  END
	  Fetch NEXT FROM EntityTypesCursor INTO @EntityType, @EntitySubType, @DefinedEntityName
	END
	CLOSE EntityTypesCursor
	DEALLOCATE EntityTypesCursor

	END